package com.annhoa.core.base;

import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.transaction.Transactional;

import org.apache.commons.beanutils.ConvertUtils;
import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import com.annhoa.util.PageData;
import com.annhoa.util.ReflectHelper;

/**
 * 
 * @author https://blog.csdn.net/dean_yang123/article/details/43102899
 *
 */
@Transactional
@Repository
public class DaoSupport implements DAO {

	//
	private static Logger logger = Logger.getLogger(DaoSupport.class.toString());

	@PersistenceContext
	EntityManager entityManager;

	
	/**
	 * * 转map 参数查询 且可以自定义返回
	 * springJpa 1.4.4
	 * @param sql
	 *            查询sql sql中的参数用:name格式
	 * @param params
	 *            查询参数map格式，key对应参数中的 :name （冒号+key）
	 * @param clazz
	 *            实体类型为空则直接转换为map格式
	 * @return
	 */
	@Deprecated
	@SuppressWarnings("unchecked")
	public List<?> queryListEntity(String sql, Map<String, Object> params, Class<?> clazz) {
		Session session = entityManager.unwrap(org.hibernate.Session.class);
		SQLQuery query = session.createSQLQuery(sql);
		if (params != null) {
			for (String key : params.keySet()) {
				query.setParameter(key, params.get(key));
			}
		}
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String, Object>> result = query.list();
		if (clazz != null) {
			List<Object> entityList = convert(clazz, result);
			return entityList;
		}
		return result;
	}

	/** 
	 * 对象参数 条件查询
	 * @author annhoa
	 * @param sql
	 * @param obj
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<?> findListEntity(String sql, Object obj ,Class<?> clazz) {
		Map<String, Object> params = null;
		if (null != obj && !obj.getClass().equals("java.util.Map")){ 
			params = ReflectHelper.getKeyAndValue(obj);  //普通对象
		}else{
			params = (Map<String, Object>) obj;
		}
		return this.queryListEntity(sql, params, clazz);
	}
	
	//???????????????//
	private List<Object> convert(Class<?> clazz, List<Map<String, Object>> list) {
		List<Object> result;
		if (CollectionUtils.isEmpty(list)) {
			return null;
		}
		result = new ArrayList<Object>();
		try {
			PropertyDescriptor[] props = Introspector.getBeanInfo(clazz).getPropertyDescriptors();
			for (Map<String, Object> map : list) {
				Object obj = clazz.newInstance();
				for (String key : map.keySet()) {
					String attrName = key.toLowerCase();
					for (PropertyDescriptor prop : props) {
						attrName = removeUnderLine(attrName);
						if (!attrName.equals(prop.getName())) {
							continue;
						}
						Method method = prop.getWriteMethod();
						Object value = map.get(key);
						if (value != null) {
							value = ConvertUtils.convert(value, prop.getPropertyType());
						}
						method.invoke(obj, value);
					}
				}
				result.add(obj);
			}
		} catch (Exception e) {
			throw new RuntimeException("数据转换错误");
		}
		return result;
	}

	private String removeUnderLine(String attrName) {
		// 去掉数据库字段的下划线
		if (attrName.contains("_")) {
			String[] names = attrName.split("_");
			String firstPart = names[0];
			String otherPart = "";
			for (int i = 1; i < names.length; i++) {
				String word = names[i].replaceFirst(names[i].substring(0, 1), names[i].substring(0, 1).toUpperCase());
				otherPart += word;
			}
			attrName = firstPart + otherPart;
		}
		return attrName;
	}

	/**
	 * 获取记录条数
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public Integer getCountBy(String sql, Map<String, Object> params) {
		Query query = entityManager.createNativeQuery(sql);
		if (params != null) {
			for (String key : params.keySet()) {
				query.setParameter(key, params.get(key));
			}
		}
		BigInteger bigInteger = (BigInteger) query.getSingleResult();
		return bigInteger.intValue();
	}
	/**
	 * @param sql
	 *            自定义sql 多参数查询 ex:String sql = "select * from user where id=?1";  
	 * 
	 * @param params
	 *            传入Object 类型参数 占位符?1 ?2 ...: ?i(?+序号)
	 * @author annhoa 2018-7-27
	 * 
	 * @return List<?> 自定义返回List对象
	 */
	public List<?> queryListEntity(String sql, Object... params) {
		Query query = entityManager.createNativeQuery(sql);
		if (params.length > 0) {
			for (int i = 0; i < params.length; i++) {
				query.setParameter(i + 1, params[i]);
			}
		}
		return query.unwrap(org.hibernate.Query.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}
	/**
	 * 新增或者删除 
	 * Map对象参数
	 * @param sql
	 * @param params
	 * @return
	 */
	public Integer deleteOrUpDate(String sql, Map<String, Object> params) {
		Query query = entityManager.createNativeQuery(sql);
		if (params != null) {
			for (String key : params.keySet()) {
				query.setParameter(key, params.get(key));
			}
		}
		return query.executeUpdate();
	}

	/**
	 * 新增或者删除
	 * Object对象参数
	 * @author annhoa
	 * @param sql
	 * @param obj
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Integer excute(String sql, Object obj) {
		Map<String, Object> params = null;
		if (null != obj && !obj.getClass().equals("java.util.Map")){ 
			params = ReflectHelper.getKeyAndValue(obj);
			
		}else{
			params = (Map<String, Object>) obj;
		}
		return this.deleteOrUpDate(sql, params);
	}

	// *****************************************实现接口***********************************//
	@Override
	public Object save(String sql, Object obj) throws Exception {
		return excute(sql, obj);
	}

	@Override
	public Object update(String sql, Object obj) throws Exception {
		return excute(sql, obj);
	}

	@Override
	public Object delete(String sql, Object obj) throws Exception {
		return excute(sql, obj);
	}

	@Override
	public Object findForObject(String sql, Object obj,Class<?> clazz) throws Exception {
		List<?> list = this.findListEntity(sql, obj,null);
		if (list.size() > 1) {
			logger.warning("返回多条sql, 请检查查询条件！");
			throw new Exception("Execute one item but many,Check you sql");

		}
		return list.get(0);  //此处取第一条
	}

	@Override
	public Object findForList(String sql, Object obj,Class<?> clazz) throws Exception {

		return this.findListEntity(sql, obj,null);
	}

	// 没实现
	@Override
	public Object findForMap(String sql, Object obj, String key, String value) throws Exception {

		return this.findListEntity(sql, obj,null);
	}
	//分页
//	@SuppressWarnings({ "rawtypes", "unchecked" })
//	@Override
//	public Page<Map> findPageByParams(String sql, Pageable pageable, Object... args) throws Exception {
//		Session session = (Session) entityManager.getDelegate();
//		org.hibernate.Query q = session.createSQLQuery(sql);
//		q.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
//		int i = 0;
//		//此处不传参数时为[{}]
//		if (args != null) {
//			for (Object arg : args) {
//				q.setParameter(i++, arg);
//			}
//		}
//		List<Map> totalCount = q.list();
//		q.setFirstResult(pageable.getPageSize() * (pageable.getPageNumber() - 1));
//		q.setMaxResults(pageable.getPageSize());
//		List<Map> pageCount = q.list();
//		Page<Map> pages = new PageImpl<>(pageCount, pageable, totalCount.size());
//		return pages;
//	}
	
	
	@SuppressWarnings({ "rawtypes" })
	@Override
	public Page<Map> findPageByParams(BasePage page) throws Exception {
		Pageable pageable = page.getPr();
		PageData pd = page.getPd();
		return GetPageMethod(page.getSql(),pageable,pd);
	}
	
	@SuppressWarnings("rawtypes")
	@Override
	public Page<Map> findPageByParams(String sql, Pageable pageable, PageData pd) throws Exception {
		return GetPageMethod(sql,pageable,pd);
	}
	
	@SuppressWarnings({ "rawtypes", "unchecked", "deprecation" })
	private Page<Map> GetPageMethod(String sql, Pageable pageable, PageData pd) throws Exception {
		Session session = (Session) entityManager.getDelegate();
		org.hibernate.Query q = session.createSQLQuery(sql);
		q.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
		int i = 0;
		if (pd != null) {
			//使用迭代器，获取key;
			Iterator<String> iter = pd.keySet().iterator();
			while(iter.hasNext()){
				q.setParameter(i++, pd.getString(iter.next()));
			}
		}
		List<Map> totalCount = q.list();
		q.setFirstResult(pageable.getPageSize() * (pageable.getPageNumber() - 1));
		q.setMaxResults(pageable.getPageSize());
		 //此处添加排序
		
		List<Map> pageCount = q.list();
		Page<Map> pages = new PageImpl<>(pageCount, pageable, totalCount.size());
		return pages;
	}
}
